package com.liuguangzhou.utils;

import com.liuguangzhou.controller.XSSFWorkbookKit;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.core.io.ClassPathResource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

public class ExcelUtil {
    static DecimalFormat decformat = new DecimalFormat("#.0000");

    /**
     * 随机生产一个名字用时间+JAVAUUID
     *
     * @return
     */
    public static String randomFileName() {
        UUID uuid = UUID.randomUUID();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
        String filename = dateFormat.format(new Date(System.currentTimeMillis())) + "-" + uuid.toString();
        return filename;
    }

    /**
     * 通过模版写入excel 如果修改templatepath=writepath即可
     *
     * @param templatepath 模版路径
     * @param writepath    写入文件路径
     * @param sheelat      第几个sheel
     * @param map          写入的值 如 A1
     * @return boolean true or false
     */
    public static boolean wirteexcel(String templatepath, String writepath, int sheelat, Map writemap) {
        File excelFile = null;
        InputStream is = null;
        try {
            excelFile = new File(templatepath);
            is = new FileInputStream(excelFile);
            XSSFWorkbook workbook = XSSFWorkbookKit.create(is);
            ;
            XSSFSheet sheet = workbook.getSheetAt(sheelat);
            Iterator it = writemap.entrySet().iterator();
            while (it.hasNext()) {
                Map.Entry pairs = (Map.Entry) it.next();
                CellReference cellReference = new CellReference((String) pairs.getKey());
                XSSFRow row = sheet.getRow(cellReference.getRow());
                XSSFCell cell = row.getCell(cellReference.getCol());
                cell.setCellValue((String) pairs.getValue());
            }
            sheet.setForceFormulaRecalculation(true);
            workbook.setForceFormulaRecalculation(true);
            FileOutputStream out = new FileOutputStream(new File(writepath));
            workbook.write(out);
            out.close();
            return true;
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 读取excel
     *
     * @param readpath 文件
     * @param sheetat  第几个单元格
     * @return Map 返回 A12=字符串类似
     */
    public static Map readexcel(String readpath, int sheetat) {
        Map readMap = new HashMap();
        File excelFile = null;
        InputStream is = null;
        try {
            excelFile = new File(readpath);
            is = new FileInputStream(excelFile);
//          XSSFWorkbook workbook = XSSFWorkbookKit.create(is);;
            XSSFWorkbook workbook = XSSFWorkbookKit.create(is);
//            XSSFWorkbook workbook = XSSFWorkbookKit.create(is);;
//            XSSFSheet sheet = workbook.getSheet("请勿修改此sheet页");
//            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            XSSFSheet sheet = workbook.getSheetAt(sheetat);
//            sheet.setForceFormulaRecalculation(true);
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                XSSFRow row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    XSSFCell cell = row.getCell(j);
                    if (cell != null) {
                        System.out.println(getCellValue(cell, "yyyyMMdd"));
//                        CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
//                        String cellkey = cellRef.formatAsString().replaceAll("\\$", "");
////                        evaluator.evaluateFormulaCell(cell); // 必须对公司进行更新否则不能读取正确的值
//                        String cellvalue = getCellValue(cell,"yyyyMMdd");
//					    System.out.println("cellkey="+ cellkey + " ，cellvalue="+cellvalue);
//                        readMap.put(cellkey, cellvalue);
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return readMap;
    }

    /**
     * 读取cell的值
     *
     * @param cell
     * @return
     */
    public static String getCellValue(XSSFCell cell, String dateFormat) {
        if (cell == null) {
            return "";
        }
        if (StringUtils.isBlank(dateFormat)) {
            dateFormat = "yyyy-MM-dd HH:mm:ss";
        }
        String value = "";
        switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_NUMERIC:
//                value = String.valueOf(cell.getNumericCellValue());//改动此处，解决科学技术法的问题。如1234.56789变成1.23456789E3
                //判断日期
                if (HSSFDateUtil.isCellDateFormatted(cell)) {//单纯的通过中文或中文字符判断，依赖于当前服务器的编码环境。这里做个测试用api自带的方法来替代
                    value = new SimpleDateFormat(dateFormat).format(cell.getDateCellValue());
//					 System.out.println("测试quota日期判断的value="+value);
                } else {
                    DecimalFormat dft = new DecimalFormat("#.########");
                    value = dft.format(cell.getNumericCellValue());
                }
                break;
            case XSSFCell.CELL_TYPE_STRING:
                value = String.valueOf(cell.getRichStringCellValue());
                break;
            case XSSFCell.CELL_TYPE_FORMULA:
                try {
//                	value = String.valueOf(cell.getNumericCellValue());//改动此处，解决科学技术法的问题。如1234.56789变成1.23456789E3
                    DecimalFormat df = new DecimalFormat("#.########");
                    value = df.format(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                    try {
                        value = String.valueOf(cell.getRichStringCellValue());
                    } catch (Exception e2) {
                        //当读取公式报错时，将cell格式修改为文本格式；当读取值为"#N/A"，则将值修改为"";
                        cell.setCellType(cell.CELL_TYPE_STRING);
                        value = getCellValue(cell, "yyyyMMdd");
                        if ("#N/A".equals(value)) {
                            value = "";
                        }
                    }

                }
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                value = "";
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case XSSFCell.CELL_TYPE_ERROR:
                value = "";
                break;
            default:
                value = "";
                break;
        }
        if (StringUtils.isNotEmpty(value)) {
            value = value.replaceAll("\u00a0|\u3000", " ");
            return value.trim();
        }
        return value;
    }

    /**
     * 读取cell的值 支持03/07版本
     *
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell, String dateFormat) {
        if (cell == null) {
            return "";
        }
        if (StringUtils.isBlank(dateFormat)) {
            dateFormat = "yyyy-MM-dd HH:mm:ss";
        }
        String value = "";
        switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_NUMERIC:
//                value = String.valueOf(cell.getNumericCellValue());//改动此处，解决科学技术法的问题。如1234.56789变成1.23456789E3
                //判断日期
                if (HSSFDateUtil.isCellDateFormatted(cell)) {//单纯的通过中文或中文字符判断，依赖于当前服务器的编码环境。这里做个测试用api自带的方法来替代
                    value = new SimpleDateFormat(dateFormat).format(cell.getDateCellValue());
//					 System.out.println("测试quota日期判断的value="+value);
                } else {
                    DecimalFormat dft = new DecimalFormat("#.########");
                    value = dft.format(cell.getNumericCellValue());
                }
                break;
            case XSSFCell.CELL_TYPE_STRING:
                value = String.valueOf(cell.getRichStringCellValue());
                break;
            case XSSFCell.CELL_TYPE_FORMULA:
                try {
//                	value = String.valueOf(cell.getNumericCellValue());//改动此处，解决科学技术法的问题。如1234.56789变成1.23456789E3
                    DecimalFormat df = new DecimalFormat("#.########");
                    value = df.format(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                    try {
                        value = String.valueOf(cell.getRichStringCellValue());
                    } catch (Exception e2) {
                        //当读取公式报错时，将cell格式修改为文本格式；当读取值为"#N/A"，则将值修改为"";
                        cell.setCellType(cell.CELL_TYPE_STRING);
                        value = getCellValue(cell, "yyyyMMdd");
                        if ("#N/A".equals(value)) {
                            value = "";
                        }
                    }

                }
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                value = "";
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case XSSFCell.CELL_TYPE_ERROR:
                value = "";
                break;
            default:
                value = "";
                break;
        }
        if (StringUtils.isNotEmpty(value)) {
            value = value.replaceAll("\u00a0|\u3000", " ");
            return value.trim();
        }
        return value;
    }


    /**
     * 读取excel
     *
     * @param readpath 文件
     * @param sheetat  第几个单元格
     * @return Map 返回 A12=字符串类似
     */
    public static List<List<String>> readExcelListLines(MultipartFile file, int sheetat, int myColNum) {
        List<List<String>> lineList = new ArrayList<List<String>>();

        MultipartFile excelFile = null;
        InputStream is = null;
        try {
            excelFile = file;
            is = file.getInputStream();
            XSSFWorkbook workbook = XSSFWorkbookKit.create(is);
            ;
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            XSSFSheet sheet = workbook.getSheetAt(sheetat);
            sheet.setForceFormulaRecalculation(true);

            //定义列数
            int colNums = 0;

            for (int i = 0; i <= sheet.getLastRowNum(); i++) {


                XSSFRow row = sheet.getRow(i);
                if (row == null) {
                    break;
                }

                //定义列数
                if (i == 0) {
                    if (myColNum > 0) {
                        colNums = myColNum;
                    } else {
                        colNums = row.getLastCellNum();
                    }
                    continue;
                }

                boolean isEmptyLine = true; //true:空行，false:不为空
                List<String> readMap = new ArrayList<String>();

                for (int j = 0; j < colNums; j++) {
                    XSSFCell cell = row.getCell(j);
                    if (cell != null) {
//                        CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
//                        String cellkey = cellRef.formatAsString().replaceAll("\\$", "");
                        evaluator.evaluateFormulaCell(cell); // 必须对公司进行更新否则不能读取正确的值
                        String cellvalue = getCellValue(cell, "yyyyMMdd");
                        if (cellvalue == null || "".equals(cellvalue.trim())) {
                            cellvalue = null;

                        } else {
                            cellvalue = cellvalue.toUpperCase();
                            isEmptyLine = false;
                        }
//					    System.out.println("cellkey==="+ j + " ，cellvalue="+cellvalue);
                        readMap.add(cellvalue);
                    } else {
                        readMap.add(null);
                    }
                }
                if (isEmptyLine) { //表示该行为空行，则跳出，不往下再解析
                    break;
                }
                lineList.add(readMap);
            }
            workbook.close();
            workbook = null;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return lineList;
    }

    public static List<List<String>> readExcelToList(MultipartFile file, int sheetat, int myColNum) {
        List<List<String>> lineList = new ArrayList<List<String>>();
        InputStream is = null;
        try {
            is = file.getInputStream();
            XSSFWorkbook workbook = XSSFWorkbookKit.create(is);
            ;
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            XSSFSheet sheet = workbook.getSheetAt(sheetat);
            sheet.setForceFormulaRecalculation(true);

            //定义列数
            int colNums = 0;

            for (int i = 0; i <= sheet.getLastRowNum(); i++) {


                XSSFRow row = sheet.getRow(i);
                if (row == null) {
                    break;
                }

                //定义列数
                if (i == 0) {
                    if (myColNum > 0) {
                        colNums = myColNum;
                    } else {
                        colNums = row.getLastCellNum();
                    }
                    continue;
                }
                boolean isEmptyLine = true; //true:空行，false:不为空
                List<String> readMap = new ArrayList<String>();

                for (int j = 0; j < colNums; j++) {
                    XSSFCell cell = row.getCell(j);
                    if (cell != null) {
//                        CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
//                        String cellkey = cellRef.formatAsString().replaceAll("\\$", "");
                        evaluator.evaluateFormulaCell(cell); // 必须对公司进行更新否则不能读取正确的值
                        String cellvalue = getCellValue(cell, "yyyyMMdd");
//                        if(cellvalue==null||"".equals(cellvalue.trim())){
//                        	cellvalue = null;
//                        }
//					    System.out.println("cellkey==="+ j + " ，cellvalue="+cellvalue);
                        //如果是All则转化为大写
                        if ("ALL".equalsIgnoreCase(cellvalue)) {
                            cellvalue = "ALL";
                        }
                        readMap.add(cellvalue);
                        if (StringUtils.isNotBlank(cellvalue)) {
                            isEmptyLine = false;
                        }
                    } else {
                        readMap.add(null);
                    }
                }
                if (isEmptyLine) { //表示该行为空行，则跳出，不往下再解析
                    break;
                }
                lineList.add(readMap);
            }
            workbook.close();
            workbook = null;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return lineList;
    }

    /**
     * 读取excel数据,支持03/07版exxcel,支持传入dateformat类型   例如yyyy-MM-dd, 如果不传默认走yyyy-MM-dd HH:mm:ss
     *
     * @param file
     * @param sheetat
     * @param myColNum
     * @param dateFormat
     * @return List<List < String>>
     * @throws
     * @author LD
     * @date 2017年4月1日 上午11:40:19
     */
    public static List<List<String>> readExcelToList(MultipartFile file, int sheetat, int myColNum, String dateFormat) {
        List<List<String>> lineList = new ArrayList<List<String>>();
        InputStream is = null;
        try {
            is = file.getInputStream();
            //XSSFWorkbook workbook = XSSFWorkbookKit.create(is);;
            Workbook workbook = XSSFWorkbookKit.createWorkBook(is);
            ;
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            //XSSFSheet sheet = workbook.getSheetAt(sheetat);
            Sheet sheet = workbook.getSheetAt(sheetat);
            sheet.setForceFormulaRecalculation(true);

            //定义列数
            int colNums = 0;

            for (int i = 0; i <= sheet.getLastRowNum(); i++) {


                //XSSFRow row = sheet.getRow(i);
                Row row = sheet.getRow(i);
                if (row == null) {
                    break;
                }

                //定义列数
                if (i == 0) {
                    if (myColNum > 0) {
                        colNums = myColNum;
                    } else {
                        colNums = row.getLastCellNum();
                    }
                    continue;
                }
                boolean isEmptyLine = true; //true:空行，false:不为空
                List<String> readMap = new ArrayList<String>();

                for (int j = 0; j < colNums; j++) {
                    //XSSFCell cell = row.getCell(j);
                    Cell cell = row.getCell(j);
                    if (cell != null) {
//                        CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
//                        String cellkey = cellRef.formatAsString().replaceAll("\\$", "");
                        evaluator.evaluateFormulaCell(cell); // 必须对公司进行更新否则不能读取正确的值
                        String cellvalue = getCellValue(cell, dateFormat);
//                        if(cellvalue==null||"".equals(cellvalue.trim())){
//                        	cellvalue = null;
//                        }
//					    System.out.println("cellkey==="+ j + " ，cellvalue="+cellvalue);
                        //如果是All则转化为大写
                        if ("ALL".equalsIgnoreCase(cellvalue)) {
                            cellvalue = "ALL";
                        }
                        readMap.add(cellvalue);
                        if (StringUtils.isNotBlank(cellvalue)) {
                            isEmptyLine = false;
                        }
                    } else {
                        readMap.add(null);
                    }
                }
                if (isEmptyLine) { //表示该行为空行，则跳出，不往下再解析
                    break;
                }
                lineList.add(readMap);
            }
            workbook.close();
            workbook = null;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return lineList;
    }

    /**
     * 解析正则文件
     *
     * @param regularFile
     * @return
     * @throws IOException
     */
    public static Map<Integer, String> parseRegularExcel(File regularFile) throws IOException {
        Map<Integer, String> regularMap = new HashMap<Integer, String>(); //存放正则map
        InputStream is = null;
        XSSFWorkbook workbook = null;
        try {
            is = new FileInputStream(regularFile);
            workbook = XSSFWorkbookKit.create(is);
            XSSFSheet sheet = workbook.getSheetAt(0);

            //定义列数
            int colNums = 0;

            for (int i = 0; i <= 1; i++) {

                XSSFRow row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }

                //定义列数
                if (i == 0) {
                    colNums = row.getLastCellNum();
                    continue;
                }

                for (int j = 0; j < colNums; j++) {
                    XSSFCell cell = row.getCell(j);
                    String cellValue = getCellValue(cell, "yyyyMMdd");
                    regularMap.put(j, cellValue);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
            throw e;
        } finally {
            if (workbook != null) {
                workbook.close();
                workbook = null;
            }
            if (is != null) {
                try {
                    is.close();
                    is = null;
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return regularMap;
    }

    public static List<Map<String, String>> parseExcelFilePMS(MultipartFile file) throws IOException {
        String tempCellValue = "";
        List<Map<String, String>> tempMapList = new ArrayList<Map<String, String>>();//用于存储EXCEL数据的集合
        XSSFWorkbook xssfWorkBook = XSSFWorkbookKit.create(file.getInputStream());
        XSSFSheet xssfSheet = xssfWorkBook.getSheetAt(0);
        String sheetName = xssfSheet.getSheetName();
        Map<String, String> titleMap = new HashMap<String, String>();//Map<列ID，列名>
        FormulaEvaluator evaluator = xssfWorkBook.getCreationHelper().createFormulaEvaluator();
        XSSFRow xssfTitleRow = xssfSheet.getRow(0);
        for (int cellNum = 0; cellNum <= xssfTitleRow.getLastCellNum(); cellNum++) {
            XSSFCell xssfCell = xssfTitleRow.getCell(cellNum);
            if (xssfCell == null) {
                continue;
            }
            titleMap.put("key" + cellNum, getXlsxValue(xssfCell));
        }
        // 循环行Row  跳过第一行标题
        for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
            XSSFRow xssfRow = xssfSheet.getRow(rowNum);
            Map<String, String> tempMap = new HashMap<String, String>();
            if (xssfRow == null) {
                break;
            }
            // 循环列Cell
            //为了节省校验时的时间，在解析时就将空行就全部过滤
            boolean isEmptyRowFlag = true;
            for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {
                XSSFCell xssfCell = xssfRow.getCell(cellNum);
                if (xssfCell == null) {
                    continue;
                }
                tempCellValue = ExcelUtil.getCellValue(xssfCell, "yyyyMMdd");
                if (xssfCell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) {
                    //evaluator.evaluateFormulaCell(xssfCell);
                    CellValue cellValue = evaluator.evaluate(xssfCell);//进行单元格公式计算
                    if ("0.0".equals(cellValue.getNumberValue() + "")) {
                        tempCellValue = cellValue.getStringValue() + "";//取值
                    } else {
                        tempCellValue = cellValue.getNumberValue() + "";//取值
                    }
                }
                tempMap.put("key" + cellNum, tempCellValue);
                if (null != tempCellValue && !"".equals(tempCellValue) && !"null".equals(tempCellValue) && !"0.0".equals(tempCellValue)) {
                    isEmptyRowFlag = false;
                }
            }
            if (isEmptyRowFlag) {//表示该行为空行，则跳出，不往下再解析
                break;
            } else {
                //glory 20150810 将行号加1，为了物料模板中上传时提示行号与模板中一致，因为模板中列头也占一行
                tempMap.put("rowNum", (rowNum + 1) + "");//该值为了存储当前行数
                tempMap.put("sheetName", sheetName);//sheet名称，用于校验模板是否正确
                tempMapList.add(tempMap);
            }
        }
        return tempMapList;
    }

    @SuppressWarnings("static-access")
    // excel2007
    private static String getXlsxValue(XSSFCell xssfCell) {
        if (xssfCell != null) {
            switch (xssfCell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(xssfCell)) {
                        Date date = xssfCell.getDateCellValue();
                        return DateKit.formatDateDefault(date);
                    } else {
                        //设置为String然后读取
//                        if (xssfCell.getColumnIndex()==0 || xssfCell.getColumnIndex()==1 || xssfCell.getColumnIndex()==2 || xssfCell.getColumnIndex()==3 || xssfCell.getColumnIndex()==4){
//                            xssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
//                            return xssfCell.getStringCellValue().trim();
//                        }else{
//                            return xssfCell.getNumericCellValue()+"";
//                        }
                        xssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        return xssfCell.getStringCellValue().trim();
                    }
                case HSSFCell.CELL_TYPE_STRING:
                    return xssfCell.getStringCellValue().trim();
                case HSSFCell.CELL_TYPE_FORMULA:
                    return xssfCell.getCellFormula().trim();
					/* xssfCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
					 return String.valueOf(xssfCell.getNumericCellValue()); */
                case HSSFCell.CELL_TYPE_BLANK:
                    return null;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    return String.valueOf(xssfCell.getBooleanCellValue()).trim();
                case HSSFCell.CELL_TYPE_ERROR:
                    System.out.println(String.valueOf(xssfCell.getErrorCellValue()).trim());
                    return null;
            }
        }
        return null;
    }


    /**
     * 正则校验excel list数据
     *
     * @param dataList   数据集合
     * @param regularMap 正则Map
     * @return
     */
    public static List<Map<String, String>> validateListData(List<List<String>> dataList, Map<Integer, String> regularMap) {
        List<Map<String, String>> errorMapList = new ArrayList<Map<String, String>>(); //存放错误数据集合
        Map<String, String> errorMap = null;
        int listSize = dataList.size();

        if (regularMap == null) {
            return errorMapList;
        }

        for (int i = 0; i < listSize; i++) {
            String rowNum = (i + 2) + "";
            errorMap = new HashMap<String, String>();
            List<String> rows = dataList.get(i);
            for (int j = 0; j < rows.size(); j++) {

                String value = rows.get(j);
                String regexStr = regularMap.get(j);
                if (StringUtils.isNotBlank(regexStr)) {
                    String[] regexArr = regexStr.split("(\r\n|\r|\n|\n\r)");
                    String regex = regexArr[0]; //正则表达式
                    String errMsg = regexArr[1]; //错误信息
                    if (value == null) {
                        value = "";
                    }
                    if (!value.matches(regex)) {
                        if (errorMap.get("reason") != null) {
                            errorMap.put("reason", errorMap.get("reason") + "；" + errMsg);
                        } else {
                            errorMap.put("reason", errMsg);
                        }
                    }
                }
            }
            if (!errorMap.isEmpty()) {
                errorMap.put("rowNum", rowNum);
                errorMapList.add(errorMap);
                if (errorMapList.size() == 100) { //当错误列表数达到100时，退出循环，后面数据不再校验
                    break;
                }
            }
        }

        return errorMapList;
    }

    public static Workbook create(InputStream in) throws
            IOException, InvalidFormatException {
        if (!in.markSupported()) {
            in = new PushbackInputStream(in, 8);
        }
        if (POIFSFileSystem.hasPOIFSHeader(in)) {
            return new HSSFWorkbook(in);
        }
        if (POIXMLDocument.hasOOXMLHeader(in)) {
            return new XSSFWorkbook(OPCPackage.open(in));
        }
        throw new IllegalArgumentException("你的excel版本目前poi解析不了");

    }

    /**
     * 创建excel文档(无模板)
     *
     * @param wb          工作簿对象
     * @param sheetName   工作表名
     * @param list        数据
     * @param keys        list中map的key数组集合
     * @param columnNames excel的列名
     * @throws IOException
     */
    public static void createWorkBook(SXSSFWorkbook wb, String sheetName, List<BeanMap<String, Object>> list, String[] keys, String columnNames[]) throws IOException {

        // 创建sheet（页），并命名
        SXSSFSheet sheet = (SXSSFSheet) wb.createSheet(sheetName);

        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for (int i = 0; i < keys.length; i++) {
            sheet.setColumnWidth((short) i, (short) (35.7 * 150));
        }

        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();

        // 创建两种字体
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 13);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 12);
        f2.setColor(IndexedColors.BLACK.getIndex());

//        Font f3=wb.createFont();
//        f3.setFontHeightInPoints((short) 10);
//        f3.setColor(IndexedColors.RED.getIndex());

        // 设置第一种单元格的样式（用于列名）
        cs.setFont(f);
        cs.setBorderLeft(CellStyle.BORDER_THIN);
        cs.setBorderRight(CellStyle.BORDER_THIN);
        cs.setBorderTop(CellStyle.BORDER_THIN);
        cs.setBorderBottom(CellStyle.BORDER_THIN);
        cs.setAlignment(CellStyle.ALIGN_CENTER);

        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);
        cs2.setBorderLeft(CellStyle.BORDER_THIN);
        cs2.setBorderRight(CellStyle.BORDER_THIN);
        cs2.setBorderTop(CellStyle.BORDER_THIN);
        cs2.setBorderBottom(CellStyle.BORDER_THIN);
        cs2.setAlignment(CellStyle.ALIGN_CENTER);

        // 创建第一行
        Row row = sheet.createRow((short) 0);

        //设置列名
        for (int i = 0; i < columnNames.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
            cell = null;
        }

        //设置每行每列的值
        for (int i = 0; i < list.size(); i++) {
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow(i + 1);
            BeanMap<String, Object> map = list.get(i);
            // 在row行上创建一个方格
            for (int j = 0; j < keys.length; j++) {
                Cell cell = row1.createCell(j);
                Object obj = map.get(keys[j]);
                cell.setCellValue(asString(obj));
                cell.setCellStyle(cs2);
                cell = null;
                obj = null;
            }
            if (i % 100 == 0) { //每当行数达到设置的值就刷新数据到硬盘,以清理内存
                sheet.flushRows();
            }
            row1 = null;
            map = null;
        }
    }

    /**
     * 根据模板文件和数据生成字节数组输出流
     *
     * @param templateFilePath 模板文件路径
     * @param list             导出的数据
     * @param keys             list中map的key数组集合
     * @return
     * @throws IOException
     */
    public static ByteArrayOutputStream writeDataToExcel(String templateFilePath, List<BeanMap<String, Object>> list, String[] keys) throws IOException {

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        File file = new File(templateFilePath);
        FileInputStream fis = new FileInputStream(file);

        //工作区

        SXSSFWorkbook wb = new SXSSFWorkbook(new XSSFWorkbook(fis));

        //获取导出数据模板第一个sheet
        SXSSFSheet sheet = (SXSSFSheet) wb.getSheetAt(0);

        //设置每行每列的值
        for (int i = 0; i < list.size(); i++) {
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow(i + 1);
            BeanMap<String, Object> map = list.get(i);
            // 在row行上创建一个方格
            for (int j = 0; j < keys.length; j++) {
                Cell cell = row1.createCell(j);
                Object obj = map.get(keys[j]);
                if (obj != null && (obj.toString().matches("^(?:0(?:\\.\\d{1,4})?|1(?:\\.0{1,4})?)$")
                        || obj.toString().matches("^?\\d+(?:\\.\\d{1,4})?$"))) {
                    cell.setCellValue(Double.parseDouble(asString(obj)));
                } else {
                    cell.setCellValue(asString(obj));
                }
                cell = null;
                obj = null;
            }
            if (i % 100 == 0) { //每当行数达到设置的值就刷新数据到硬盘,以清理内存
                sheet.flushRows();
            }
            row1 = null;
            map = null;
        }

        wb.write(os);
        wb.close();
        wb = null;

        return os;
    }

    /**
     * 根据模板文件和数据生成字节数组输出流(按生成类型去生成文件)
     *
     * @param templateFilePath 模板文件路径
     * @param list             导出的数据
     * @param keys             list中map的key数组集合
     * @return
     * @throws IOException
     */
    public static ByteArrayOutputStream writeDataToExcelType(String templateFilePath, List<BeanMap<String, Object>> list, String[] keys) throws IOException {

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        File file = new File(templateFilePath);
        FileInputStream fis = new FileInputStream(file);

        //工作区

        SXSSFWorkbook wb = new SXSSFWorkbook(new XSSFWorkbook(fis));

        //获取导出数据模板第一个sheet
        SXSSFSheet sheet = (SXSSFSheet) wb.getSheetAt(0);

        //设置每行每列的值
        for (int i = 0; i < list.size(); i++) {
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow(i + 1);
            BeanMap<String, Object> map = list.get(i);
            // 在row行上创建一个方格
            for (int j = 0; j < keys.length; j++) {
                Cell cell = row1.createCell(j);
                Object obj = map.get(keys[j]);
                setCellValue(cell, obj);
            }
            if (i % 100 == 0) { //每当行数达到设置的值就刷新数据到硬盘,以清理内存
                sheet.flushRows();
            }
            row1 = null;
            map = null;
        }

        wb.write(os);
        wb.close();
        wb = null;

        return os;
    }


    /**
     * 根据模板文件和数据生成字节数组输出流
     *
     * @param templateFilePath 模板文件路径
     * @param list             导出的数据
     * @param keys             list中map的key数组集合
     * @param cellTypeMap      列类型map
     * @return
     * @throws IOException
     */
    public static ByteArrayOutputStream writeDataToExcel(String templateFilePath, List<BeanMap<String, Object>> list, String[] keys, Map<String, String> cellTypeMap) throws IOException {

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        File file = new File(templateFilePath);
        FileInputStream fis = new FileInputStream(file);

        //工作区

        SXSSFWorkbook wb = new SXSSFWorkbook(new XSSFWorkbook(fis));

        //获取导出数据模板第一个sheet
        SXSSFSheet sheet = (SXSSFSheet) wb.getSheetAt(0);

        //设置每行每列的值
        for (int i = 0; i < list.size(); i++) {
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow(i + 1);
            BeanMap<String, Object> map = list.get(i);
            // 在row行上创建一个方格
            for (int j = 0; j < keys.length; j++) {
                Cell cell = row1.createCell(j);
                Object obj = map.get(keys[j]);
                setCellValue(cell, cellTypeMap.get(keys[j]), asString(obj));
                cell = null;
                obj = null;
            }
            if (i % 100 == 0) { //每当行数达到设置的值就刷新数据到硬盘,以清理内存
                sheet.flushRows();
            }
            row1 = null;
            map = null;
        }

        wb.write(os);
        wb.close();
        wb = null;

        return os;
    }

    /**
     * 根据List列表信息生成Excel流
     *
     * @param fileName                     下载文件名称
     * @param lstBMap                      List列表信息
     * @param columnNameMap<显示列名称，对应表的列名称>
     * @return
     * @throws Exception
     * @author conyon
     * @date 2019年4月11日 下午5:31:34
     */
    public static ResponseEntity<byte[]> writeDataToExcel(String fileName,
                                                          List<BeanMap> lstBMap, LinkedHashMap<String, String> columnNameMap) throws Exception {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            //工作区

            SXSSFWorkbook wb = new SXSSFWorkbook(new XSSFWorkbook());

            //获取导出数据模板第一个sheet
            SXSSFSheet sheet = (SXSSFSheet) wb.createSheet();


            if (lstBMap != null && lstBMap.size() > 0) {
                //columnIndexMap<所处列号，表中列名称>
                Map<Integer, String> columnIndexMap = new HashMap<Integer, String>();
                int rowIndex = 0;
                Row rowTitle = sheet.createRow(rowIndex++);
                int columnIndex = 0;
                for (Map.Entry<String, String> entry : columnNameMap.entrySet()) {
                    Cell cell = rowTitle.createCell(columnIndex);
                    setCellValue(cell, entry.getKey());
                    columnIndexMap.put(columnIndex, entry.getValue());
                    columnIndex++;
                }

                //设置每行每列的值
                for (int i = 0; i < lstBMap.size(); i++) {
                    Row row = sheet.createRow(i + 1);
                    BeanMap map = lstBMap.get(i);

                    for (int j = 0; j < columnIndex; j++) {
                        Cell cell = row.createCell(j);
                        String key = columnIndexMap.get(j);
                        String value = map.getObjectToString(key);
                        setCellValue(cell, value);
                    }
                }
            }
            wb.write(os);
            wb.close();
            wb = null;
            String downloadFileName = fileName + ".xlsx";
            HttpHeaders headers = new HttpHeaders();
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            headers.setContentDispositionFormData("attachment", new String(downloadFileName.getBytes(), "ISO-8859-1"));
            headers.setContentLength(os.size());
            return new ResponseEntity<byte[]>(os.toByteArray(), headers, HttpStatus.OK);
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
    }


    /**
     * 转化obj为String
     *
     * @param obj
     * @return
     */
    private static String asString(Object obj) {
        return obj != null ? obj.toString() : "";
    }

    /**
     * 根据cellType设置列值
     *
     * @param cell
     * @param cellType
     * @param value
     */
    private static void setCellValue(Cell cell, String cellType, String value) {
        if (StringUtils.isNotBlank(cellType)) {
            if (cellType.equals("double") && StringUtils.isNotBlank(value) && isNumber(value)) {
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellValue(Double.parseDouble(value));
            } else if (cellType.equals("double2") && StringUtils.isNotBlank(value) && isNumber(value)) {
                DecimalFormat dft = new DecimalFormat("0.00");
                value = dft.format(Double.parseDouble(value));
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellValue(Double.parseDouble(value));
            } else if (cellType.equals("double4") && StringUtils.isNotBlank(value) && isNumber(value)) {
                DecimalFormat dft = new DecimalFormat("0.0000");
                value = dft.format(Double.parseDouble(value));
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellValue(Double.parseDouble(value));
            } else {
                cell.setCellValue(value);
            }
        } else {
            cell.setCellValue(value);
        }
    }

    /**
     * 判断是否为数值
     *
     * @param str
     * @return boolean
     * @throws
     * @author LD
     * @date 2016年7月21日 下午2:58:33
     */
    public static boolean isNumber(String str) {
        Pattern pattern = Pattern.compile("^-?(([1-9][0-9]{0,2}((,[0-9]{3})*|[0-9]*))|0)([\\.]{0,1}[0-9]*)?$");
        return pattern.matcher(str).matches();
    }

    /**
     * 可借鉴的，针对解析excel内容细微判断的方法
     * @param obj
     * @return public static String getCellValue(Cell cell) {
    if (cell == null) {
    return "";
    }

    int cellType = cell.getCellType();
    String dataStr = "";
    if (cellType == Cell.CELL_TYPE_NUMERIC) {
    //是数值型的字段
    if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
    double cellValue = cell.getNumericCellValue();
    Date date = HSSFDateUtil.getJavaDate(cellValue);
    SimpleDateFormat dformat = new SimpleDateFormat(DateUtil.FORMAT_DATE_YYYY_MM_DD_HH_MM_SS);
    dataStr = dformat.format(date);
    } else {
    CellStyle style = cell.getCellStyle();
    if(style != null){
    short dateFormat = style.getDataFormat();
    if(dateFormat == 183 || dateFormat == 31
    || dateFormat == 188 || dateFormat == 57){
    double cellValue = cell.getNumericCellValue();
    dataStr = cellValue + "";
    Date date = HSSFDateUtil.getJavaDate(cellValue);
    Date date2 = DateUtil.parse(dataStr, DateUtil.FORMAT_DATE_YYYY_MM_DD_HH_MM_SS);
    if (date != null && date2 != null) {
    SimpleDateFormat dformat = new SimpleDateFormat(DateUtil.FORMAT_DATE_YYYY_MM_DD_HH_MM_SS);
    dataStr = dformat.format(date);
    }
    }else {
    String temp = style.getDataFormatString();
    if (temp.equals("General") || temp.equals("@")) { // 常规或文本
    HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
    dataStr = dataFormatter.formatCellValue(cell);
    } else {
    DecimalFormat df = new DecimalFormat("#.##########");
    dataStr = df.format(cell.getNumericCellValue());
    }
    }
    }else{
    cell.setCellType(Cell.CELL_TYPE_STRING);
    dataStr = cell.getStringCellValue();
    }
    }
    } else if (cellType == Cell.CELL_TYPE_STRING) {
    String str = cell.getStringCellValue();
    str = StringUtil.toDBC(str);
    dataStr = str;
    } else if (cellType == Cell.CELL_TYPE_FORMULA) {
    int type = cell.getCachedFormulaResultType();
    if (type == Cell.CELL_TYPE_NUMERIC) {
    dataStr = String.valueOf(cell.getNumericCellValue());

    } else if (type == Cell.CELL_TYPE_STRING) {
    String str = cell.getStringCellValue();
    str = StringUtil.toDBC(str);
    dataStr = str;
    } else if (type == Cell.CELL_TYPE_BOOLEAN) {
    if (cell.getBooleanCellValue()) {
    dataStr = "1";
    } else {
    dataStr = "0";
    }
    } else if (type == Cell.CELL_TYPE_ERROR) {
    dataStr = String.valueOf(cell.getErrorCellValue());
    }
    } else if (cellType == Cell.CELL_TYPE_BLANK) {
    dataStr = "";
    } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
    if (cell.getBooleanCellValue()) {
    dataStr = "1";
    } else {
    dataStr = "0";
    }
    } else if (cellType == Cell.CELL_TYPE_ERROR) {
    dataStr = String.valueOf(cell.getErrorCellValue());
    } else {
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    dataStr = cell.getStringCellValue();
    }
    if (StringUtil.isNotEmpty(dataStr)) {
    dataStr = dataStr.trim();
    }
    return dataStr;
    }*/


    /**
     * 给单元格赋值，并且动态列宽
     *
     * @param sheet
     * @param rowIndex
     * @param cellIndex
     * @param str
     */
    public static void setAutoCell(XSSFSheet sheet, int rowIndex, int cellIndex, String str) {
        XSSFCell cell = getCell(sheet, rowIndex, cellIndex);
        cell.setCellValue(str);
        setColumnWidth(sheet, cellIndex, cell);
    }

    /**
     * 获取单元格（没有创建）
     *
     * @param sheet
     * @param rowIndex
     * @param cellIndex
     * @return
     */
    public static XSSFCell getCell(XSSFSheet sheet, int rowIndex, int cellIndex) {
        XSSFRow row = getRow(sheet, rowIndex);
        XSSFCell cell = row.getCell(cellIndex);
        if (cell == null) {
            cell = row.createCell(cellIndex);
        }
        return cell;
    }

    /**
     * 获取单元格（没有创建）
     *
     * @param sheet
     * @param rowIndex
     * @param cellIndex
     * @return
     */
    public static XSSFCell getCell(XSSFRow row, int cellIndex) {
        XSSFCell cell = row.getCell(cellIndex);
        if (cell == null) {
            cell = row.createCell(cellIndex);
        }
        return cell;
    }

    /**
     * 获取行（没有创建）
     *
     * @param sheet
     * @param rowIndex
     * @return
     */
    public static XSSFRow getRow(XSSFSheet sheet, int rowIndex) {
        XSSFRow row = sheet.getRow(rowIndex);
        if (row == null) {
            row = sheet.createRow(rowIndex);
        }
        return row;
    }

    /**
     * 设置列宽度
     *
     * @param sheet
     * @param colIndex（列号）
     * @param colWidth（字符）
     */
    public static void setColumnWidth(XSSFSheet sheet, int colIndex, int colWidth) {
        sheet.setColumnWidth(colIndex, colWidth * 256);
    }

    /**
     * 锁定sheet时指定那些区域可以编辑
     *
     * @param wb
     * @param sheet
     * @param startRowindex
     * @param endRowindex
     * @param startColindex
     * @param endColindex
     */
    public static void unlockArea(XSSFWorkbook wb, XSSFSheet sheet, int startRowindex, int endRowindex, int startColindex, int endColindex) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        for (int i = startRowindex; i <= endRowindex; i++) {
            for (int j = startColindex; j <= endColindex; j++) {
                XSSFCell cell = getCell(sheet, i, j);
                cellStyle.setLocked(false);
                cell.setCellStyle(cellStyle);
            }
        }
        sheet.protectSheet(new String("bpm"));
    }

    /**
     * 设置区域单元格自动列宽度（根据字段长度）
     *
     * @param sheet
     * @param rowStartIndex
     * @param rowEndIndex
     * @param cellStartIndex
     * @param cellEndIndex
     */
    public static void setAutoColumnWidth(XSSFSheet sheet, int rowStartIndex, int rowEndIndex, int cellStartIndex, int cellEndIndex) {

        for (int i = rowStartIndex; i < rowEndIndex; i++) {
            XSSFRow row = getRow(sheet, i);
            for (int j = cellStartIndex; j < cellEndIndex; j++) {
                XSSFCell cell = row.getCell(j);
                if (cell == null) {
                    cell = row.createCell(j);
                }
                setColumnWidth(sheet, j, cell);
            }
        }
    }

    /**
     * 设置根据同一列单元格内容最长的长度设置列宽（超过40个字符则默认40）
     *
     * @param sheet
     * @param cellIndex
     * @param cell
     */
    private static void setColumnWidth(XSSFSheet sheet, int cellIndex, XSSFCell cell) {
        int newColWidth = getCellValueBytes(cell).length * 256 + 1 * 256;
        int colWidth = sheet.getColumnWidth(cellIndex);
        if (colWidth < newColWidth) {
            if (newColWidth > 13824) {
                newColWidth = 10240;
            }
            sheet.setColumnWidth(cellIndex, newColWidth);//自动设置宽度
        }
    }

    /**
     * 获取单元格内容byte
     *
     * @param cell
     * @return
     */
    private static byte[] getCellValueBytes(XSSFCell cell) {
        String cellValue = "";
        DecimalFormat df = new DecimalFormat("#");
        switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:
                cellValue = cell.getRichStringCellValue().getString().trim();
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
//            cellValue = df.format(cell.getNumericCellValue()).toString();  
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
                break;
            case XSSFCell.CELL_TYPE_FORMULA:
                cellValue = cell.getCellFormula();
                break;
            default:
                cellValue = "";
        }
        return cellValue.getBytes();
    }

    /**
     * 单元格赋值
     *
     * @param xCell
     * @param oValue 值
     * @return
     */
    public static void setCellValue(Cell xCell, Object oValue) {
        if (oValue == null) {
            xCell.setCellValue("");
        } else if (oValue instanceof Integer) {
            xCell.setCellValue(((Integer) oValue).intValue());
        } else if (oValue instanceof String) {
            xCell.setCellValue(((String) oValue));
        } else if (oValue instanceof Double) {
            double d_value = ((Double) oValue).doubleValue();
            double value = Double.valueOf(decformat.format((d_value)));
            xCell.setCellValue(value);
        } else if (oValue instanceof Float) {
            xCell.setCellValue(((Float) oValue).floatValue());
        } else if (oValue instanceof Long) {
            xCell.setCellValue(((Long) oValue).longValue());
        } else if (oValue instanceof Boolean) {
            xCell.setCellValue(((Boolean) oValue).booleanValue());
        } else if (oValue instanceof Date) {
            xCell.setCellValue(((Date) oValue));
        } else if (oValue instanceof BigDecimal) {
            xCell.setCellValue(((BigDecimal) oValue).doubleValue());
        }
    }


    /**
     * 读取excel数据,支持03/07版exxcel,支持传入dateformat类型   例如yyyy-MM-dd, 如果不传默认走yyyy-MM-dd HH:mm:ss
     *
     * @param file
     * @param sheetat
     * @param myColNum
     * @param dateFormat
     * @return List<List < String>>
     * @throws
     * @author LD
     * @date 2017年4月1日 上午11:40:19
     */
    public static List<Map<String, String>> readExcelToMap(MultipartFile file, int sheetat, int myColNum, String dateFormat) {
        List<Map<String, String>> lineList = new ArrayList<Map<String, String>>();
        InputStream is = null;
        try {
            is = file.getInputStream();
            //XSSFWorkbook workbook = XSSFWorkbookKit.create(is);;
            Workbook workbook = XSSFWorkbookKit.createWorkBook(is);
            ;
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            //XSSFSheet sheet = workbook.getSheetAt(sheetat);
            Sheet sheet = workbook.getSheetAt(sheetat);
            sheet.setForceFormulaRecalculation(true);

            //定义列数
            int colNums = 0;

            for (int i = 0; i <= sheet.getLastRowNum(); i++) {


                //XSSFRow row = sheet.getRow(i);
                Row row = sheet.getRow(i);
                if (row == null) {
                    break;
                }

                //定义列数
                if (i == 0) {
                    if (myColNum > 0) {
                        colNums = myColNum;
                    } else {
                        colNums = row.getLastCellNum();
                    }
                    continue;
                }
                boolean isEmptyLine = true; //true:空行，false:不为空
                Map<String, String> readMap = new HashMap<String, String>();

                for (int j = 0; j < colNums; j++) {
                    //XSSFCell cell = row.getCell(j);
                    Cell cell = row.getCell(j);
                    if (cell != null) {
//                        CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
//                        String cellkey = cellRef.formatAsString().replaceAll("\\$", "");
                        evaluator.evaluateFormulaCell(cell); // 必须对公司进行更新否则不能读取正确的值
                        String cellvalue = getCellValue(cell, dateFormat);
//                        if(cellvalue==null||"".equals(cellvalue.trim())){
//                        	cellvalue = null;
//                        }
//					    System.out.println("cellkey==="+ j + " ，cellvalue="+cellvalue);
                        //如果是All则转化为大写
                        if ("ALL".equalsIgnoreCase(cellvalue)) {
                            cellvalue = "ALL";
                        }
                        readMap.put("key" + j, cellvalue);
                        if (StringUtils.isNotBlank(cellvalue)) {
                            isEmptyLine = false;
                        }
                    } else {
                        readMap.put("key" + j, null);
                    }
                }
                if (isEmptyLine) { //表示该行为空行，则跳出，不往下再解析
                    break;
                }
                lineList.add(readMap);
            }
            workbook.close();
            workbook = null;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return lineList;
    }

    public static void main(String[] args) {

//    	ExcelUtil.readexcel("C:/Users/Administrator/Desktop/PMS/需求/中压一次模板-2 - 副本 - 副本.xlsx", 0);
        ExcelUtil.readexcel("C:/Users/Administrator/Desktop/中压一次(产品上传模板3)-20150828.xlsx", 0);
        //ExcelUtil.readexcel("C:/Users/admin/Desktop/Quota@limitation for channel 2015_NEW upload.xlsx", 0);

//    	ExcelUtil.readexcel("C:/Users/admin/Desktop/Quota Actual_Limitation Upload Template (7).xlsx", 0);

        String value = " ";
        System.out.println(StringUtils.isNotBlank(value));
    }


    public static Map<String, Map<Integer,String>>  exceRegular(String fileName){
        Map<String, Map<Integer,String>> RegularMap = new HashMap<String, Map<Integer,String>>();

        try {
            RegularMap.clear(); //清空正则map

            ClassPathResource classPathResource = new ClassPathResource("resources/regularExcel");

            System.out.println(classPathResource.getFile()+ File.separator + fileName);

            File regularFile = new File(classPathResource.getFile()+ File.separator + fileName);
            if (!regularFile.isDirectory()) {
                RegularMap.put(regularFile.getName().replaceAll(".xlsx", ""), ExcelUtil.parseRegularExcel(regularFile));
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
        return RegularMap;
    }

}

